
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 07/18/2013 14:02:31
-- Generated from EDMX file: D:\src\GitRepo\bitbucket\wabo-office\Wabo.Dal\Model.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [Wabo];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_Logs_Users]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Logs] DROP CONSTRAINT [FK_Logs_Users];
GO
IF OBJECT_ID(N'[dbo].[FK_ModuleAcl_Groups]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ModuleAcl] DROP CONSTRAINT [FK_ModuleAcl_Groups];
GO
IF OBJECT_ID(N'[dbo].[FK_ModuleAcl_Modules]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ModuleAcl] DROP CONSTRAINT [FK_ModuleAcl_Modules];
GO
IF OBJECT_ID(N'[dbo].[FK_UserGroup_Users]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserGroups] DROP CONSTRAINT [FK_UserGroup_Users];
GO
IF OBJECT_ID(N'[dbo].[FK_UserGroups_Groups]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserGroups] DROP CONSTRAINT [FK_UserGroups_Groups];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Groups]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Groups];
GO
IF OBJECT_ID(N'[dbo].[Logs]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Logs];
GO
IF OBJECT_ID(N'[dbo].[ModuleAcl]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ModuleAcl];
GO
IF OBJECT_ID(N'[dbo].[Modules]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Modules];
GO
IF OBJECT_ID(N'[dbo].[SystemSettings]', 'U') IS NOT NULL
    DROP TABLE [dbo].[SystemSettings];
GO
IF OBJECT_ID(N'[dbo].[UserGroups]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserGroups];
GO
IF OBJECT_ID(N'[dbo].[Users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Groups'
CREATE TABLE [dbo].[Groups] (
    [id] bigint IDENTITY(1,1) NOT NULL,
    [name] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'ModuleAcl'
CREATE TABLE [dbo].[ModuleAcl] (
    [id] bigint IDENTITY(1,1) NOT NULL,
    [module_id] bigint  NOT NULL,
    [group_id] bigint  NOT NULL,
    [access_read] bit  NOT NULL,
    [access_write] bit  NOT NULL,
    [access_delete] bit  NOT NULL,
    [access_list] bit  NOT NULL
);
GO

-- Creating table 'Modules'
CREATE TABLE [dbo].[Modules] (
    [id] bigint IDENTITY(1,1) NOT NULL,
    [title] nvarchar(150)  NOT NULL,
    [controller] nvarchar(100)  NOT NULL,
    [order] smallint  NOT NULL,
    [show_on_menu] bit  NOT NULL
);
GO

-- Creating table 'UserGroups'
CREATE TABLE [dbo].[UserGroups] (
    [id] bigint IDENTITY(1,1) NOT NULL,
    [group_id] bigint  NOT NULL,
    [user_id] bigint  NOT NULL
);
GO

-- Creating table 'Users'
CREATE TABLE [dbo].[Users] (
    [id] bigint IDENTITY(1,1) NOT NULL,
    [username] nvarchar(50)  NOT NULL,
    [password] nvarchar(150)  NOT NULL,
    [salt] nvarchar(30)  NOT NULL,
    [email] nvarchar(150)  NOT NULL,
    [name] nvarchar(50)  NULL,
    [last_name] nvarchar(50)  NULL,
    [locked] bit  NOT NULL,
    [domain] nvarchar(200)  NULL
);
GO

-- Creating table 'Logs'
CREATE TABLE [dbo].[Logs] (
    [id] bigint IDENTITY(1,1) NOT NULL,
    [user_id] bigint  NOT NULL,
    [date] datetime  NOT NULL,
    [description] nvarchar(100)  NULL,
    [log_operation_type] int  NOT NULL,
    [module] nvarchar(50)  NOT NULL,
    [action] nvarchar(128)  NOT NULL
);
GO

-- Creating table 'SystemSettings'
CREATE TABLE [dbo].[SystemSettings] (
    [id] bigint IDENTITY(1,1) NOT NULL,
    [application_name] nvarchar(250)  NOT NULL,
    [max_login_attempts] smallint  NOT NULL,
    [login_lock_timeout] int  NOT NULL,
    [log_items_per_page] int  NOT NULL,
    [theme] nvarchar(80)  NOT NULL,
    [enable_active_directory_login] bit  NOT NULL,
    [days_to_purge] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [id] in table 'Groups'
ALTER TABLE [dbo].[Groups]
ADD CONSTRAINT [PK_Groups]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'ModuleAcl'
ALTER TABLE [dbo].[ModuleAcl]
ADD CONSTRAINT [PK_ModuleAcl]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'Modules'
ALTER TABLE [dbo].[Modules]
ADD CONSTRAINT [PK_Modules]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'UserGroups'
ALTER TABLE [dbo].[UserGroups]
ADD CONSTRAINT [PK_UserGroups]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'Logs'
ALTER TABLE [dbo].[Logs]
ADD CONSTRAINT [PK_Logs]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'SystemSettings'
ALTER TABLE [dbo].[SystemSettings]
ADD CONSTRAINT [PK_SystemSettings]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [group_id] in table 'ModuleAcl'
ALTER TABLE [dbo].[ModuleAcl]
ADD CONSTRAINT [FK_ModuleAcl_Groups]
    FOREIGN KEY ([group_id])
    REFERENCES [dbo].[Groups]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ModuleAcl_Groups'
CREATE INDEX [IX_FK_ModuleAcl_Groups]
ON [dbo].[ModuleAcl]
    ([group_id]);
GO

-- Creating foreign key on [group_id] in table 'UserGroups'
ALTER TABLE [dbo].[UserGroups]
ADD CONSTRAINT [FK_UserGroups_Groups]
    FOREIGN KEY ([group_id])
    REFERENCES [dbo].[Groups]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserGroups_Groups'
CREATE INDEX [IX_FK_UserGroups_Groups]
ON [dbo].[UserGroups]
    ([group_id]);
GO

-- Creating foreign key on [module_id] in table 'ModuleAcl'
ALTER TABLE [dbo].[ModuleAcl]
ADD CONSTRAINT [FK_ModuleAcl_Modules]
    FOREIGN KEY ([module_id])
    REFERENCES [dbo].[Modules]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ModuleAcl_Modules'
CREATE INDEX [IX_FK_ModuleAcl_Modules]
ON [dbo].[ModuleAcl]
    ([module_id]);
GO

-- Creating foreign key on [user_id] in table 'UserGroups'
ALTER TABLE [dbo].[UserGroups]
ADD CONSTRAINT [FK_UserGroup_Users]
    FOREIGN KEY ([user_id])
    REFERENCES [dbo].[Users]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserGroup_Users'
CREATE INDEX [IX_FK_UserGroup_Users]
ON [dbo].[UserGroups]
    ([user_id]);
GO

-- Creating foreign key on [user_id] in table 'Logs'
ALTER TABLE [dbo].[Logs]
ADD CONSTRAINT [FK_Logs_Users]
    FOREIGN KEY ([user_id])
    REFERENCES [dbo].[Users]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Logs_Users'
CREATE INDEX [IX_FK_Logs_Users]
ON [dbo].[Logs]
    ([user_id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------